﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.DAL;
using SWDataDB.Models;
using System.Data.SqlClient;

namespace SWDataDB.DAL
{
    /// <summary>
    /// 著录表操作(2013-5-19：zhhw)
    /// 暂时没有完成，后期使用时候再添加函数
    /// </summary>
    public class ZLInfo
    {
        /// <summary>
        /// 获取数据的条数
        /// </summary>
        /// <returns></returns>
        public long GetZLInfoCount()
        {
            long lCount = 0;
            string strSql = "SELECT COUNT(*) AS count FROM dbo.ZL_TBL";
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            DataRow dr = dt.Rows[0];
            lCount = long.Parse(dr["count"].ToString());

            return lCount;
        }

        /// <summary>
        /// ZL是否存在
        /// </summary>
        /// <param name="file_id">FILE_ID</param>
        /// <returns></returns>
        public bool IsZLExist(string file_id)
        {
            string strSql = "SELECT COUNT(*) AS COUNT FROM dbo.ZL_TBL WHERE FILE_ID='" + file_id + "'";
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            DataRow dr = dt.Rows[0];
            if (int.Parse(dr["COUNT"].ToString()) > 0)
            {
                return true;
            }else  return false;
        }

        /// <summary>
        /// 得到所有记录
        /// </summary>
        /// <returns>返回dataset</returns>
        public DataSet GetAllInfoWithSet()
        {
            string strSql = "SELECT *FROM dbo.ZL_TBL";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }



        public DataSet GetAllInfoWithSet(int id)
        {
            string strSql = "SELECT *FROM dbo.ZL_TBL WHERE ID =" + id  ;
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        public DataSet GetAllInfobyTitle(string key)
        {
            string strSql = "SELECT *FROM dbo.ZL_TBL WHERE TITLE like '%"+ key +"%'" ;
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }




        public DataSet GetAllInfobyInput(string keyword)
        {
            string strSql = "SELECT *FROM dbo.ZL_TBL WHERE TITLE like '%" + keyword + "%'or FILE_ID like '%" + keyword + "%'or PEOJ_NAME like '%" + keyword + "%'or MINE_NAME like '%" + keyword + "%'or HX_PEOPLE like '%" + keyword + "%'or PEOJ_CODE like '%" + keyword + "%'or DATA_CLASS like '%" + keyword + "%'or ADMINREG_CODE like '%" + keyword + "%'or SHEET_NAME like '%" + keyword + "%'or SHEET_ID like '%" + keyword + "%'or MAJOR_MINE like '%" + keyword + "%'or PUTIN_DATE like '%" + keyword + "%'";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到所有记录
        /// </summary>
        /// <returns>返回datatable</returns>
        public DataTable GetAllInfoWithTable()
        {
            string strSql = "SELECT *FROM dbo.ZL_TBL";
            return SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到著录表记录for 分页显示，升序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByASC(string strExp)
        {
            string strSql = "";

            if (string.IsNullOrEmpty(strExp))
            {
                strSql = "SELECT * FROM dbo.ZL_TBL ";
            }

            else
            {
                strSql = "SELECT * FROM dbo.ZL_TBL WHERE ";
                strSql += strExp;
                strSql += "ORDER BY ID ASC";
            }


            DataSet result = SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);

            return result;

        }

        /// <summary>
        /// 返回空间查询结果（郑华伟需求）
        /// </summary>
        /// <param name="strExp"></param>
        /// <returns></returns>
        public DataSet GetInfoForSpaceQueryByASC(string strExp)
        {
            string strSql = "";

            if (string.IsNullOrEmpty(strExp))
            {
                strSql = "SELECT FILE_ID, TITLE,START_LONGITUDE, START_LATITUDE, END_LONGITUDE, END_LATITUDE FROM dbo.ZL_TBL ";
            }

            else
            {
                strSql = "SELECT FILE_ID, TITLE,START_LONGITUDE, START_LATITUDE, END_LONGITUDE, END_LATITUDE FROM dbo.ZL_TBL WHERE ";
                strSql += strExp;
                strSql += "ORDER BY ID ASC";
            }


            DataSet result = SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);

            return result;

        }

        /// <summary>
        /// 得到著录表记录for 分页显示，降序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByDESC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.ZL_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID DESC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 返回空间查询结果（郑华伟需求）,只返回查询的档号题名和经纬度
        /// </summary>
        /// <param name="strExp"></param>
        /// <returns></returns>
        public DataSet GetInfoForSpaceQueryByDESC(string strExp)
        {
            string strSql = "";

            if (string.IsNullOrEmpty(strExp))
            {
                strSql = "SELECT FILE_ID, TITLE,START_LONGITUDE, START_LATITUDE, END_LONGITUDE, END_LATITUDE FROM dbo.ZL_TBL ";
            }

            else
            {
                strSql = "SELECT FILE_ID, TITLE,START_LONGITUDE, START_LATITUDE, END_LONGITUDE, END_LATITUDE FROM dbo.ZL_TBL WHERE ";
                strSql += strExp;
                strSql += "ORDER BY ID DESC";
            }


            DataSet result = SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);

            return result;

        }

        /// <summary>
        /// 分页查询 后台控制页面大小
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回IList</returns>
        public IList<ZL_TBL> GetListByPage(long page, long num, string strExp)
        {
            string sql = SQLHelper.GetPagingSQL("dbo.ZL_TBL", "ID", "*", strExp, "ID DESC", num, page);
            IList<ZL_TBL> zlLst = new List<ZL_TBL>();
            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, sql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    ZL_TBL zl_info = new ZL_TBL();
                    zl_info.O_GC_DEPART = dr["GC_DEPART"].ToString();
                    zl_info.O_FILE_ID = dr["FILE_ID"].ToString();
                    zl_info.O_TITLE = dr["TITLE"].ToString();
                    zl_info.O_UNIT = dr["UNIT"].ToString();
                    zl_info.O_HX_PEOPLE = dr["HX_PEOPLE"].ToString();
                    zl_info.O_PEOJ_NAME = dr["PEOJ_NAME"].ToString();
                    zl_info.O_PEOJ_CODE = dr["PEOJ_CODE"].ToString();
                    zl_info.O_DATA_CLASS = dr["DATA_CLASS"].ToString();
                    zl_info.O_CLASS_CODE = dr["CLASS_CODE"].ToString();
                    zl_info.O_ADMIN_REGION = dr["ADMIN_REGION"].ToString();
                    zl_info.O_ADMINREG_CODE = dr["ADMINREG_CODE"].ToString();
                    zl_info.O_MINE_NAME = dr["MINE_NAME"].ToString();
                    zl_info.O_SHEET_NAME = dr["SHEET_NAME"].ToString();
                    zl_info.O_SHEET_ID = dr["SHEET_ID"].ToString();
                    zl_info.O_WORK_LEVEL = dr["WORK_LEVEL"].ToString();
                    zl_info.O_WORKLEVEL_ID = dr["WORKLEVEL_ID"].ToString();
                    zl_info.O_START_DATE = dr["START_DATE"].ToString();
                    zl_info.O_END_DATE = dr["END_DATE"].ToString();
                    zl_info.O_START_LONGITUDE = int.Parse(dr["START_LONGITUDE"].ToString());
                    zl_info.O_END_LATITUDE = int.Parse(dr["END_LATITUDE"].ToString());
                    zl_info.O_MAJOR_MINE = dr["MAJOR_MINE"].ToString();
                    zl_info.O_MINE_CODE = dr["MINE_CODE"].ToString();
                    zl_info.O_PUTIN_DATE = dr["PUTIN_DATE"].ToString();
                    zl_info.O_TRANSF_CODE = dr["TRANSF_CODE"].ToString();
                    zl_info.O_SECU_LEVEL = dr["SECU_LEVEL"].ToString();
                    zl_info.O_SECU_ID = dr["SECU_ID"].ToString();
                    zl_info.O_SECU_ITEMS = dr["SECU_ITEMS"].ToString();
                    zl_info.O_PROTECT_DATE =dr["PROTECT_DATE"].ToString();
                    zl_info.O_DEAD_LINE = dr["DEAD_LINE"].ToString();
                    zl_info.O_CG_UNIT = dr["CG_UNIT"].ToString();
                    zl_info.O_CG_CODE = dr["CG_CODE"].ToString();
                    zl_info.O_YS_UNIT = dr["YS_UNIT"].ToString();
                    zl_info.O_YS_CODE = dr["YS_CODE"].ToString();
                    zl_info.O_KEY_WORDS = dr["KEY_WORDS"].ToString();
                    zl_info.O_ABSTRACT = dr["ABSTRACT"].ToString();
                    //zl_info.o_ = dr["REMARK"].ToString();
                    zlLst.Add(zl_info);
                }
                /*GC_DEPART,FILE_ID,TITLE,UNIT,HX_PEOPLE,PEOJ_NAME,PEOJ_CODE,DATA_CLASS,CLASS_CODE,
                 * ADMIN_REGION,ADMINREG_CODE,MINE_NAME,SHEET_NAME,SHEET_ID,WORK_LEVEL,WORKLEVEL_ID,
                 * START_DATE,END_DATE,START_LONGITUDE,END_LATITUDE,MAJOR_MINE,MINE_CODE,PUTIN_DATE,
                 * TRANSF_CODE,SECU_LEVEL,SECU_ID,SECU_ITEMS,PROTECT_DATE,DEAD_LINE,CG_UNIT,CG_CODE,
                 * YS_UNIT,YS_CODE,KEY_WORDS,ABSTRACT,REMARK
                 */
                return zlLst;
            }
            catch (ArgumentNullException e)
            {
                throw e;
            }
            catch (FormatException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 根据条件查询(xxx = 'asdf')
        /// </summary>
        /// <param name="exp"></param>
        /// <returns>返回dataset</returns>
        public DataSet SelectZLInfoByEepSet(string exp)
        {
            string strSql = "SELECT * FROM dbo.ZL_TBL WHERE ";
            strSql += exp;
            try
            {
                DataSet ds = new DataSet();
                ds = SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);

                return ds;
            }
            catch (ArgumentNullException e)
            {
                throw e;
            }
            catch (FormatException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 根据条件查询(xxx = 'asdf')
        /// </summary>
        /// <param name="exp"></param>
        /// <returns>返回List</returns>
        public IList<ZL_TBL> SelectZLInfoByEepList(string exp)
        {
            IList<ZL_TBL> zlLst = new List<ZL_TBL>();
            string strSql = "SELECT * FROM dbo.ZL_TBL WHERE ";
            strSql += exp;
            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    ZL_TBL zl_info = new ZL_TBL();
                    zl_info.O_GC_DEPART = dr["GC_DEPART"].ToString();
                    zl_info.O_FILE_ID = dr["FILE_ID"].ToString();
                    zl_info.O_TITLE = dr["TITLE"].ToString();
                    zl_info.O_UNIT = dr["UNIT"].ToString();
                    zl_info.O_HX_PEOPLE = dr["HX_PEOPLE"].ToString();
                    zl_info.O_PEOJ_NAME = dr["PEOJ_NAME"].ToString();
                    zl_info.O_PEOJ_CODE = dr["PEOJ_CODE"].ToString();
                    zl_info.O_DATA_CLASS = dr["DATA_CLASS"].ToString();
                    zl_info.O_CLASS_CODE = dr["CLASS_CODE"].ToString();
                    zl_info.O_ADMIN_REGION = dr["ADMIN_REGION"].ToString();
                    zl_info.O_ADMINREG_CODE = dr["ADMINREG_CODE"].ToString();
                    zl_info.O_MINE_NAME = dr["MINE_NAME"].ToString();
                    zl_info.O_SHEET_NAME = dr["SHEET_NAME"].ToString();
                    zl_info.O_SHEET_ID = dr["SHEET_ID"].ToString();
                    zl_info.O_WORK_LEVEL = dr["WORK_LEVEL"].ToString();
                    zl_info.O_WORKLEVEL_ID = dr["WORKLEVEL_ID"].ToString();
                    zl_info.O_START_DATE = dr["START_DATE"].ToString();
                    zl_info.O_END_DATE = dr["END_DATE"].ToString();
                    zl_info.O_START_LONGITUDE = int.Parse(dr["START_LONGITUDE"].ToString());
                    zl_info.O_START_LATITUDE = int.Parse(dr["START_LATITUDE"].ToString());
                    zl_info.O_END_LONGITUDE = int.Parse(dr["END_LONGITUDE"].ToString());
                    zl_info.O_END_LATITUDE = int.Parse(dr["END_LATITUDE"].ToString());
                    zl_info.O_MAJOR_MINE = dr["MAJOR_MINE"].ToString();
                    zl_info.O_MINE_CODE = dr["MINE_CODE"].ToString();
                    zl_info.O_PUTIN_DATE = dr["PUTIN_DATE"].ToString();
                    zl_info.O_TRANSF_CODE = dr["TRANSF_CODE"].ToString();

                    zl_info.O_YXIN_NUM_ZL = int.Parse(dr["YXIN_NUM_ZL"].ToString());
                    zl_info.O_YXIN_NUM_X = int.Parse(dr["YXIN_NUM_X"].ToString());
                    zl_info.O_YXIN_NUM_M = int.Parse(dr["YXIN_NUM_M"].ToString());
                    zl_info.O_YXIE_NUM_ZL = int.Parse(dr["YXIE_NUM_ZL"].ToString());
                    zl_info.O_YXIE_NUM_X = int.Parse(dr["YXIE_NUM_X"].ToString());
                    zl_info.O_YXIE_NUM_D = int.Parse(dr["YXIE_NUM_D"].ToString());
                    zl_info.O_BB_NUM_X = int.Parse(dr["BB_NUM_X"].ToString());
                    zl_info.O_BB_NUM_K = int.Parse(dr["BB_NUM_K"].ToString());
                    zl_info.O_BB_NUM_K_BIG = int.Parse(dr["BB_NUM_K_BIG"].ToString());
                    zl_info.O_GP_NUM_H = int.Parse(dr["GP_NUM_H"].ToString());
                    zl_info.O_GP_NUM_J = int.Parse(dr["GP_NUM_J"].ToString());
                    zl_info.O_YP_NUM_X = int.Parse(dr["YP_NUM_X"].ToString());
                    zl_info.O_YP_NUM_D = int.Parse(dr["YP_NUM_D"].ToString());
                    zl_info.O_BP_NUM_H = int.Parse(dr["BP_NUM_H"].ToString());
                    zl_info.O_BP_NUM_J = int.Parse(dr["BP_NUM_J"].ToString());
                    zl_info.O_QTSW_NUM_X = int.Parse(dr["QTSW_NUM_X"].ToString());
                    zl_info.O_QTSW_NUM_J = int.Parse(dr["QTSW_NUM_J"].ToString());
                    zl_info.O_XGZL_NUM_W = int.Parse(dr["XGZL_NUM_W"].ToString());
                    zl_info.O_XGZL_NUM_T = int.Parse(dr["XGZL_NUM_T"].ToString());
                    zl_info.O_XGZL_NUM_J = int.Parse(dr["XGZL_NUM_J"].ToString());

                    zl_info.O_SECU_LEVEL = dr["SECU_LEVEL"].ToString();
                    zl_info.O_SECU_ID = dr["SECU_ID"].ToString();
                    zl_info.O_SECU_ITEMS = dr["SECU_ITEMS"].ToString();
                    zl_info.O_PROTECT_DATE = dr["PROTECT_DATE"].ToString();
                    zl_info.O_DEAD_LINE = dr["DEAD_LINE"].ToString();
                    zl_info.O_CG_UNIT = dr["CG_UNIT"].ToString();
                    zl_info.O_CG_CODE = dr["CG_CODE"].ToString();
                    zl_info.O_YS_UNIT = dr["YS_UNIT"].ToString();
                    zl_info.O_YS_CODE = dr["YS_CODE"].ToString();
                    zl_info.O_KEY_WORDS = dr["KEY_WORDS"].ToString();
                    zl_info.O_ABSTRACT = dr["ABSTRACT"].ToString();
                    //zl_info.o_ = dr["REMARK"].ToString();
                    zlLst.Add(zl_info);
                }
                return zlLst;
            }
            catch (ArgumentNullException e)
            {
                throw e; 
            }
            catch (FormatException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 通过档号查找著录表
        /// </summary>
        /// <param name="fileId"></param>
        /// <returns>返回List</returns>
        public ZL_TBL SelectZLInfoByID(string fileId)
        {
            ZL_TBL zl_info = new ZL_TBL();
            string strSql = "SELECT * FROM dbo.ZL_TBL WHERE FILE_ID ='";
            strSql += fileId + "'";
            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    zl_info.O_GC_DEPART = dr["GC_DEPART"].ToString();
                    zl_info.O_FILE_ID = dr["FILE_ID"].ToString();
                    zl_info.O_TITLE = dr["TITLE"].ToString();
                    zl_info.O_UNIT = dr["UNIT"].ToString();
                    zl_info.O_HX_PEOPLE = dr["HX_PEOPLE"].ToString();
                    zl_info.O_PEOJ_NAME = dr["PEOJ_NAME"].ToString();
                    zl_info.O_PEOJ_CODE = dr["PEOJ_CODE"].ToString();
                    zl_info.O_DATA_CLASS = dr["DATA_CLASS"].ToString();
                    zl_info.O_CLASS_CODE = dr["CLASS_CODE"].ToString();
                    zl_info.O_ADMIN_REGION = dr["ADMIN_REGION"].ToString();
                    zl_info.O_ADMINREG_CODE = dr["ADMINREG_CODE"].ToString();
                    zl_info.O_MINE_NAME = dr["MINE_NAME"].ToString();
                    zl_info.O_SHEET_NAME = dr["SHEET_NAME"].ToString();
                    zl_info.O_SHEET_ID = dr["SHEET_ID"].ToString();
                    zl_info.O_WORK_LEVEL = dr["WORK_LEVEL"].ToString();
                    zl_info.O_WORKLEVEL_ID = dr["WORKLEVEL_ID"].ToString();
                    zl_info.O_START_DATE = dr["START_DATE"].ToString();
                    zl_info.O_END_DATE = dr["END_DATE"].ToString();
                    zl_info.O_START_LONGITUDE = int.Parse(dr["START_LONGITUDE"].ToString());
                    zl_info.O_START_LATITUDE = int.Parse(dr["START_LATITUDE"].ToString());
                    zl_info.O_END_LONGITUDE = int.Parse(dr["END_LONGITUDE"].ToString());
                    zl_info.O_END_LATITUDE = int.Parse(dr["END_LATITUDE"].ToString());
                    zl_info.O_MAJOR_MINE = dr["MAJOR_MINE"].ToString();
                    zl_info.O_MINE_CODE = dr["MINE_CODE"].ToString();
                    zl_info.O_PUTIN_DATE = dr["PUTIN_DATE"].ToString();
                    zl_info.O_TRANSF_CODE = dr["TRANSF_CODE"].ToString();

                    zl_info.O_YXIN_NUM_ZL = int.Parse(dr["YXIN_NUM_ZL"].ToString());
                    zl_info.O_YXIN_NUM_X = int.Parse(dr["YXIN_NUM_X"].ToString());
                    zl_info.O_YXIN_NUM_M = int.Parse(dr["YXIN_NUM_M"].ToString());
                    zl_info.O_YXIE_NUM_ZL = int.Parse(dr["YXIE_NUM_ZL"].ToString());
                    zl_info.O_YXIE_NUM_X = int.Parse(dr["YXIE_NUM_X"].ToString());
                    zl_info.O_YXIE_NUM_D = int.Parse(dr["YXIE_NUM_D"].ToString());
                    zl_info.O_BB_NUM_X = int.Parse(dr["BB_NUM_X"].ToString());
                    zl_info.O_BB_NUM_K = int.Parse(dr["BB_NUM_K"].ToString());
                    zl_info.O_BB_NUM_K_BIG = int.Parse(dr["BB_NUM_K_BIG"].ToString());
                    zl_info.O_GP_NUM_H = int.Parse(dr["GP_NUM_H"].ToString());
                    zl_info.O_GP_NUM_J = int.Parse(dr["GP_NUM_J"].ToString());
                    zl_info.O_YP_NUM_X = int.Parse(dr["YP_NUM_X"].ToString());
                    zl_info.O_YP_NUM_D = int.Parse(dr["YP_NUM_D"].ToString());
                    zl_info.O_BP_NUM_H = int.Parse(dr["BP_NUM_H"].ToString());
                    zl_info.O_BP_NUM_J = int.Parse(dr["BP_NUM_J"].ToString());
                    zl_info.O_QTSW_NUM_X = int.Parse(dr["QTSW_NUM_X"].ToString());
                    zl_info.O_QTSW_NUM_J = int.Parse(dr["QTSW_NUM_J"].ToString());
                    zl_info.O_XGZL_NUM_W = int.Parse(dr["XGZL_NUM_W"].ToString());
                    zl_info.O_XGZL_NUM_T = int.Parse(dr["XGZL_NUM_T"].ToString());
                    zl_info.O_XGZL_NUM_J = int.Parse(dr["XGZL_NUM_J"].ToString());

                    zl_info.O_SECU_LEVEL = dr["SECU_LEVEL"].ToString();
                    zl_info.O_SECU_ID = dr["SECU_ID"].ToString();
                    zl_info.O_SECU_ITEMS = dr["SECU_ITEMS"].ToString();
                    zl_info.O_PROTECT_DATE = dr["PROTECT_DATE"].ToString();
                    zl_info.O_DEAD_LINE = dr["DEAD_LINE"].ToString();
                    zl_info.O_CG_UNIT = dr["CG_UNIT"].ToString();
                    zl_info.O_CG_CODE = dr["CG_CODE"].ToString();
                    zl_info.O_YS_UNIT = dr["YS_UNIT"].ToString();
                    zl_info.O_YS_CODE = dr["YS_CODE"].ToString();
                    zl_info.O_KEY_WORDS = dr["KEY_WORDS"].ToString();
                    zl_info.O_ABSTRACT = dr["ABSTRACT"].ToString();
                }

                return zl_info;
            }

            catch (ArgumentNullException e)
            {
                throw e;
            }
            catch (FormatException e)
            {
                throw e;
            }
        }


        /// <summary>
        /// 添加著录表数据
        /// </summary>
        /// <param name="zl_info"></param>
        /// <returns></returns>
        public int AddZLInfo(ZL_TBL zl_info)
        {
            // 先处理普通数据
            string strSql = "INSERT INTO dbo.ZL_TBL(GC_DEPART,FILE_ID,TITLE,UNIT,HX_PEOPLE,";
            strSql += "PEOJ_NAME,PEOJ_CODE,DATA_CLASS,CLASS_CODE,ADMIN_REGION,ADMINREG_CODE,MINE_NAME,SHEET_NAME,";
            strSql += "SHEET_ID,WORK_LEVEL,WORKLEVEL_ID,START_DATE,END_DATE,START_LONGITUDE,START_LATITUDE,END_LONGITUDE,";
            strSql += "END_LATITUDE,MAJOR_MINE,MINE_CODE,PUTIN_DATE,TRANSF_CODE,YXIN_NUM_ZL,YXIN_NUM_X,YXIN_NUM_M,";
            strSql += "YXIE_NUM_ZL,YXIE_NUM_X,YXIE_NUM_D,BB_NUM_X,BB_NUM_K,BB_NUM_K_BIG,GP_NUM_H,GP_NUM_J,YP_NUM_X,";
            strSql += "YP_NUM_D,BP_NUM_H,BP_NUM_J,QTSW_NUM_X,QTSW_NUM_J,XGZL_NUM_W,XGZL_NUM_T,XGZL_NUM_J,";
            strSql += "SECU_LEVEL,SECU_ID,SECU_ITEMS,PROTECT_DATE,DEAD_LINE,CG_UNIT,CG_CODE,YS_UNIT,";
            strSql += "YS_CODE,KEY_WORDS,ABSTRACT,REMARK) VALUES(";

            StringBuilder sbSql = new StringBuilder(strSql);

            strSql += "'" + zl_info.O_GC_DEPART + "',";
            strSql += "'" + zl_info.O_FILE_ID + "',";
            strSql += "'" + zl_info.O_TITLE + "',";
            strSql += "'" + zl_info.O_UNIT + "',";
            strSql += "'" + zl_info.O_HX_PEOPLE + "',";
            strSql += "'" + zl_info.O_PEOJ_NAME + "',";
            strSql += "'" + zl_info.O_PEOJ_CODE + "',";
            strSql += "'" + zl_info.O_DATA_CLASS + "',";
            strSql += "'" + zl_info.O_CLASS_CODE + "',";
            strSql += "'" + zl_info.O_ADMIN_REGION + "',";

            strSql += "'" + zl_info.O_ADMINREG_CODE + "',";
            strSql += "'" + zl_info.O_MINE_NAME + "',";
            strSql += "'" + zl_info.O_SHEET_NAME + "',";
            strSql += "'" + zl_info.O_SHEET_ID + "',";
            strSql += "'" + zl_info.O_WORK_LEVEL + "',";
            strSql += "'" + zl_info.O_WORKLEVEL_ID + "',";
            strSql += "'" + zl_info.O_START_DATE + "',";
            strSql += "'" + zl_info.O_END_DATE + "',";
            strSql += zl_info.O_START_LONGITUDE.ToString() + ",";
            strSql += zl_info.O_START_LATITUDE.ToString() + ",";


            strSql += zl_info.O_END_LONGITUDE.ToString() + ",";
            strSql += zl_info.O_END_LATITUDE.ToString() + ",";
            strSql += "'" + zl_info.O_MAJOR_MINE + "',";
            strSql += "'" + zl_info.O_MINE_CODE + "',";
            strSql += "'" + zl_info.O_PUTIN_DATE + "',";
            strSql += "'" + zl_info.O_TRANSF_CODE + "',";
            strSql += zl_info.O_YXIN_NUM_ZL.ToString() + ",";
            strSql += zl_info.O_YXIN_NUM_X.ToString() + ",";
            strSql += zl_info.O_YXIN_NUM_M.ToString() + ",";
            strSql += zl_info.O_YXIE_NUM_ZL.ToString() + ",";

            strSql += zl_info.O_YXIE_NUM_X.ToString() + ",";
            strSql += zl_info.O_YXIE_NUM_D.ToString() + ",";
            strSql += zl_info.O_BB_NUM_X.ToString() + ",";
            strSql += zl_info.O_BB_NUM_K.ToString() + ",";
            strSql += zl_info.O_BB_NUM_K_BIG.ToString() + ",";
            strSql += zl_info.O_GP_NUM_H.ToString() + ",";
            strSql += zl_info.O_GP_NUM_J.ToString() + ",";
            strSql += zl_info.O_YP_NUM_X.ToString() + ",";

            strSql += zl_info.O_YP_NUM_D.ToString() + ",";
            strSql += zl_info.O_BP_NUM_H.ToString() + ",";
            strSql += zl_info.O_BP_NUM_J.ToString() + ",";
            strSql += zl_info.O_QTSW_NUM_X.ToString() + ",";
            strSql += zl_info.O_QTSW_NUM_J.ToString() + ",";
            strSql += zl_info.O_XGZL_NUM_W.ToString() + ",";
            strSql += zl_info.O_XGZL_NUM_T.ToString() + ",";
            strSql += zl_info.O_XGZL_NUM_J.ToString() + ",";
            strSql += "'" + zl_info.O_SECU_LEVEL + "',";
            strSql += "'" + zl_info.O_SECU_ID + "',";

            strSql += "'" + zl_info.O_SECU_ITEMS + "',";
            strSql += "'" + zl_info.O_PROTECT_DATE + "',";
            strSql += "'" + zl_info.O_DEAD_LINE + "',";
            strSql += "'" + zl_info.O_CG_UNIT + "',";
            strSql += "'" + zl_info.O_CG_CODE + "',";
            strSql += "'" + zl_info.O_YS_UNIT + "',";
            strSql += "'" + zl_info.O_YS_CODE + "',";
            strSql += "'" + zl_info.O_KEY_WORDS + "',";
            strSql += "'" + zl_info.O_ABSTRACT + "',";
            strSql += "'" + zl_info.O_REMARK + "')";
            
            

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);


        }

        /// <summary>
        /// 通过ID删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteZLInfoByID(int id)
        {
            string strSql = "DELETE FROM dbo.ZL_TBL WHERE ID ='";
            strSql += id + "'";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 根据档号删除著录表数据
        /// </summary>
        /// <param name="file_id">著录表档号</param>
        /// <returns></returns>
        public int DeleteZLInfo(string file_id)
        {
            string strSql = "DELETE FROM dbo.ZL_TBL WHERE FILE_ID ='";
            strSql += file_id + "'";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 更新著录表
        /// </summary>
        /// <param name="zl_info"></param>
        /// <returns></returns>
        public int UpdateZLInfo(ZL_TBL zl_info)
        {
            /*FILE_ID,ZK_CODE,KT_LCODE,ZK_LONGITUDE,ZK_LATITUDE,ZK_X,ZK_Y,ZK_H,
            ZK_DEPTH,ZK_CORELEN,GC_CORELEN,ZKYX_COUNT,GCYX_COUNT,START_DATE,END_DATE;*/

            string strSql = "UPDATE dbo.ZL_TBL ";

            strSql += "SET GC_DEPART='" + zl_info.O_GC_DEPART + "',";
            strSql += "FILE_ID='" + zl_info.O_FILE_ID + "',";
            strSql += "TITLE='" + zl_info.O_TITLE + "',";
            strSql += "UNIT='" + zl_info.O_UNIT + "',";
            strSql += "HX_PEOPLE='" + zl_info.O_HX_PEOPLE + "',";
            strSql += " PEOJ_NAME='" + zl_info.O_PEOJ_NAME + "',";
            strSql += " PEOJ_CODE='" + zl_info.O_PEOJ_CODE + "',";
            strSql += " DATA_CLASS='" + zl_info.O_DATA_CLASS + "',";
            strSql += " CLASS_CODE='" + zl_info.O_CLASS_CODE + "',";
            strSql += " ADMIN_REGION='" + zl_info.O_ADMIN_REGION + "',";
            strSql += " ADMINREG_CODE='" + zl_info.O_ADMINREG_CODE + "',";
            strSql += " MINE_NAME='" + zl_info.O_MINE_NAME + "',";
            strSql += " SHEET_NAME='" + zl_info.O_SHEET_NAME + "',";
            strSql += " SHEET_ID='" + zl_info.O_SHEET_ID + "',";
            strSql += " WORK_LEVEL='" + zl_info.O_WORK_LEVEL + "',";
            strSql += " WORKLEVEL_ID='" + zl_info.O_WORKLEVEL_ID + "',";
            strSql += " START_DATE='" + zl_info.O_START_DATE + "',";
            strSql += " END_DATE='" + zl_info.O_END_DATE + "',";
            strSql += " START_LONGITUDE=" + zl_info.O_START_LONGITUDE.ToString() + ",";
            strSql += " START_LATITUDE=" + zl_info.O_START_LATITUDE.ToString() + ",";
            strSql += " END_LONGITUDE=" + zl_info.O_END_LONGITUDE.ToString() + ",";
            strSql += " END_LATITUDE=" + zl_info.O_END_LATITUDE.ToString() + ",";

            strSql += " MAJOR_MINE='" + zl_info.O_MAJOR_MINE + "',";
            strSql += " MINE_CODE='" + zl_info.O_MINE_CODE + "',";
            strSql += " PUTIN_DATE='" + zl_info.O_PUTIN_DATE + "',";
            strSql += " TRANSF_CODE='" + zl_info.O_TRANSF_CODE + "',";

            strSql += " YXIN_NUM_ZL=" + zl_info.O_YXIN_NUM_ZL.ToString() + ",";
            strSql += " YXIN_NUM_X=" + zl_info.O_YXIN_NUM_X.ToString() + ",";
            strSql += " YXIN_NUM_M=" + zl_info.O_YXIN_NUM_M.ToString() + ",";
            strSql += " YXIE_NUM_ZL=" + zl_info.O_YXIE_NUM_ZL.ToString() + ",";
            strSql += " YXIE_NUM_X=" + zl_info.O_YXIE_NUM_X.ToString() + ",";
            strSql += " YXIE_NUM_D=" + zl_info.O_YXIE_NUM_D.ToString() + ",";
            strSql += " BB_NUM_X=" + zl_info.O_BB_NUM_X.ToString() + ",";
            strSql += " BB_NUM_K=" + zl_info.O_BB_NUM_K.ToString() + ",";
            strSql += " BB_NUM_K_BIG=" + zl_info.O_BB_NUM_K_BIG.ToString() + ",";
            strSql += " GP_NUM_H=" + zl_info.O_GP_NUM_H.ToString() + ",";
            strSql += " GP_NUM_J=" + zl_info.O_GP_NUM_J.ToString() + ",";
            strSql += " YP_NUM_X=" + zl_info.O_YP_NUM_X.ToString() + ",";
            strSql += " YP_NUM_D=" + zl_info.O_YP_NUM_D.ToString() + ",";
            strSql += " BP_NUM_H=" + zl_info.O_BP_NUM_H.ToString() + ",";
            strSql += " BP_NUM_J=" + zl_info.O_BP_NUM_J.ToString() + ",";
            strSql += " QTSW_NUM_X=" + zl_info.O_QTSW_NUM_X.ToString() + ",";
            strSql += " QTSW_NUM_J=" + zl_info.O_QTSW_NUM_J.ToString() + ",";
            strSql += " XGZL_NUM_W=" + zl_info.O_XGZL_NUM_W.ToString() + ",";
            strSql += " XGZL_NUM_T=" + zl_info.O_XGZL_NUM_T.ToString() + ",";
            strSql += " XGZL_NUM_J=" + zl_info.O_XGZL_NUM_J.ToString() + ",";
            strSql += " SECU_LEVEL='" + zl_info.O_SECU_LEVEL + "',";
            strSql += " SECU_ID='" + zl_info.O_SECU_ID + "',";
            strSql += " SECU_ITEMS='" + zl_info.O_SECU_ITEMS + "',";
            strSql += " PROTECT_DATE='" + zl_info.O_PROTECT_DATE + "',";
            strSql += " DEAD_LINE='" + zl_info.O_DEAD_LINE + "',";
            strSql += " CG_UNIT='" + zl_info.O_CG_UNIT + "',";
            strSql += " CG_CODE='" + zl_info.O_CG_CODE + "',";
            strSql += " YS_UNIT='" + zl_info.O_YS_UNIT + "',";
            strSql += " YS_CODE='" + zl_info.O_YS_CODE + "',";
            strSql += " KEY_WORDS='" + zl_info.O_KEY_WORDS + "',";
            strSql += " ABSTRACT='" + zl_info.O_ABSTRACT + "',";
            strSql += " REMARK='" + zl_info.O_REMARK + "'";

            strSql += "WHERE FILE_ID='" + zl_info.O_FILE_ID + "'";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 调用存储过程 插入或更新
        /// </summary>
        /// <param name="zl_info"></param>
        /// <returns></returns>
        public int UpdateOrInsertZLInfo(ZL_TBL zl_info)
        {
            string strSql = "ZL_Insert_OR_Update";

           SqlParameter[] arrSqlPara = new SqlParameter[]
           {

               new SqlParameter("@i_GC_DEPART",zl_info.O_GC_DEPART),
               new SqlParameter("@i_FILE_ID",zl_info.O_FILE_ID),
               new SqlParameter("@i_TITLE",zl_info.O_TITLE),
               new SqlParameter("@i_UNIT",zl_info.O_UNIT),
               new SqlParameter("@i_HX_PEOPLE",zl_info.O_HX_PEOPLE),
               new SqlParameter("@i_PEOJ_NAME",zl_info.O_PEOJ_NAME),
               new SqlParameter("@i_PEOJ_CODE",zl_info.O_PEOJ_CODE),
               new SqlParameter("@i_DATA_CLASS",zl_info.O_DATA_CLASS),
               new SqlParameter("@i_CLASS_CODE",zl_info.O_CLASS_CODE),
               new SqlParameter("@i_ADMIN_REGION",zl_info.O_ADMIN_REGION),
               new SqlParameter("@i_ADMINREG_CODE",zl_info.O_ADMINREG_CODE),
               new SqlParameter("@i_MINE_NAME",zl_info.O_MINE_NAME),
               new SqlParameter("@i_SHEET_NAME",zl_info.O_SHEET_NAME),
               new SqlParameter("@i_SHEET_ID",zl_info.O_SHEET_ID),
               new SqlParameter("@i_WORK_LEVEL",zl_info.O_WORK_LEVEL),
               new SqlParameter("@i_WORKLEVEL_ID",zl_info.O_WORKLEVEL_ID),
               new SqlParameter("@i_START_DATE",zl_info.O_START_DATE),
               new SqlParameter("@i_END_DATE",zl_info.O_END_DATE),
               new SqlParameter("@i_START_LONGITUDE",zl_info.O_START_LONGITUDE),
               new SqlParameter("@i_END_LONGITUDE",zl_info.O_START_LATITUDE),
               new SqlParameter("@i_START_LATITUDE",zl_info.O_END_LONGITUDE),
               new SqlParameter("@i_END_LATITUDE",zl_info.O_END_LATITUDE),
               new SqlParameter("@i_MAJOR_MINE",zl_info.O_MAJOR_MINE),
               new SqlParameter("@i_MINE_CODE",zl_info.O_MINE_CODE),
               new SqlParameter("@i_PUTIN_DATE",zl_info.O_PUTIN_DATE),
               new SqlParameter("@i_TRANSF_CODE",zl_info.O_TRANSF_CODE),
               new SqlParameter("@i_YXIN_NUM_ZL",zl_info.O_YXIN_NUM_ZL),
               new SqlParameter("@i_YXIN_NUM_X",zl_info.O_YXIN_NUM_X),
               new SqlParameter("@i_YXIN_NUM_M",zl_info.O_YXIN_NUM_M),
               new SqlParameter("@i_YXIE_NUM_ZL",zl_info.O_YXIE_NUM_ZL),
               new SqlParameter("@i_YXIE_NUM_X",zl_info.O_YXIE_NUM_X),
               new SqlParameter("@i_YXIE_NUM_D",zl_info.O_YXIE_NUM_D),
               new SqlParameter("@i_BB_NUM_X",zl_info.O_BB_NUM_X),
               new SqlParameter("@i_BB_NUM_K",zl_info.O_BB_NUM_K),
               new SqlParameter("@i_BB_NUM_K_BIG",zl_info.O_BB_NUM_K_BIG),
               new SqlParameter("@i_GP_NUM_H",zl_info.O_GP_NUM_H),
               new SqlParameter("@i_GP_NUM_J",zl_info.O_GP_NUM_J),
               new SqlParameter("@i_YP_NUM_X",zl_info.O_YP_NUM_X),
               new SqlParameter("@i_YP_NUM_D",zl_info.O_YP_NUM_D),
               new SqlParameter("@i_BP_NUM_H",zl_info.O_BP_NUM_H),
               new SqlParameter("@i_BP_NUM_J",zl_info.O_BP_NUM_J),
               new SqlParameter("@i_QTSW_NUM_X",zl_info.O_QTSW_NUM_X),
               new SqlParameter("@i_QTSW_NUM_J",zl_info.O_QTSW_NUM_J),
               new SqlParameter("@i_XGZL_NUM_W",zl_info.O_XGZL_NUM_W),
               new SqlParameter("@i_XGZL_NUM_T",zl_info.O_XGZL_NUM_T),
               new SqlParameter("@i_XGZL_NUM_J",zl_info.O_XGZL_NUM_J),
               new SqlParameter("@i_SECU_LEVEL",zl_info.O_SECU_LEVEL),
               new SqlParameter("@i_SECU_ID",zl_info.O_SECU_ID),
               new SqlParameter("@i_SECU_ITEMS",zl_info.O_SECU_ITEMS),
               new SqlParameter("@i_PROTECT_DATE", zl_info.O_PROTECT_DATE),
               new SqlParameter("@i_DEAD_LINE",zl_info.O_DEAD_LINE),
               new SqlParameter("@i_CG_UNIT",zl_info.O_CG_UNIT),
               new SqlParameter("@i_CG_CODE",zl_info.O_CG_CODE),
               new SqlParameter("@i_YS_UNIT",zl_info.O_YS_UNIT),
               new SqlParameter("@i_YS_CODE",zl_info.O_YS_CODE),
               new SqlParameter("@i_KEY_WORDS",zl_info.O_KEY_WORDS),
               new SqlParameter("@i_ABSTRACT",zl_info.O_ABSTRACT),
               new SqlParameter("@i_REMARK",zl_info.O_REMARK)
           };

           return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.StoredProcedure, strSql, arrSqlPara);
        }
    }
}
